import { Router, Request, Response } from 'express';
import { getDatabase } from '../database/init';
import { authenticateToken } from '../middleware/auth';

const router = Router();

// 所有洞察路由都需要认证
router.use(authenticateToken);

// 获取数据状态概览
router.get('/data-status', async (req: Request, res: Response) => {
  try {
    const db = getDatabase();
    const userId = req.userId;
    const userIdStr = String(userId);

    // 获取总任务数和完成任务数
    const overallStatsResult = await db.query(`
      SELECT 
        COUNT(*) as total_tasks,
        COUNT(CASE WHEN completed = true THEN 1 END) as completed_tasks
      FROM tasks 
      WHERE user_id = $1::text
    `, [userIdStr]);

    // 获取最近30天任务数
    const recent30DaysResult = await db.query(`
      SELECT COUNT(*) as tasks_count
      FROM tasks 
      WHERE user_id = $1::text 
        AND created_at >= NOW() - INTERVAL '30 days'
    `, [userIdStr]);

    // 获取最近60天任务数
    const recent60DaysResult = await db.query(`
      SELECT COUNT(*) as tasks_count
      FROM tasks 
      WHERE user_id = $1::text 
        AND created_at >= NOW() - INTERVAL '60 days'
    `, [userIdStr]);

    // 获取有数据的分类数量
    const categoriesResult = await db.query(`
      SELECT COUNT(DISTINCT category) as categories_count
      FROM tasks 
      WHERE user_id = $1::text 
        AND completed = true
        AND created_at >= NOW() - INTERVAL '90 days'
    `, [userIdStr]);

    // 获取有数据的优先级数量
    const prioritiesResult = await db.query(`
      SELECT COUNT(DISTINCT priority) as priorities_count
      FROM tasks 
      WHERE user_id = $1::text 
        AND completed = true
        AND created_at >= NOW() - INTERVAL '90 days'
    `, [userIdStr]);

    // 检查各功能的数据充足性
    const workPatternsReady = recent30DaysResult.rows[0].tasks_count >= 5;
    const suggestionsReady = recent30DaysResult.rows[0].tasks_count >= 10;
    const procrastinationReady = categoriesResult.rows[0].categories_count >= 2;
    const predictionsReady = overallStatsResult.rows[0].completed_tasks >= 15;

    res.json({
      success: true,
      data: {
        totalTasks: parseInt(overallStatsResult.rows[0].total_tasks),
        completedTasks: parseInt(overallStatsResult.rows[0].completed_tasks),
        tasksLast30Days: parseInt(recent30DaysResult.rows[0].tasks_count),
        tasksLast60Days: parseInt(recent60DaysResult.rows[0].tasks_count),
        categoriesWithData: parseInt(categoriesResult.rows[0].categories_count),
        prioritiesWithData: parseInt(prioritiesResult.rows[0].priorities_count),
        readiness: {
          workPatterns: workPatternsReady,
          suggestions: suggestionsReady,
          procrastination: procrastinationReady,
          predictions: predictionsReady,
          overall: workPatternsReady && suggestionsReady
        }
      }
    });
  } catch (error) {
    console.error('获取数据状态失败:', error);
    res.status(500).json({ error: '获取数据状态失败' });
  }
});

// 获取工作模式分析
router.get('/work-patterns', async (req: Request, res: Response) => {
  try {
    const db = getDatabase();
    const userId = req.userId;
    const userIdStr = String(userId);

    // 分析任务创建的时间分布
    const hourlyCreationResult = await db.query(`
      SELECT 
        EXTRACT(HOUR FROM created_at) as hour,
        COUNT(*) as count
      FROM tasks 
      WHERE user_id = $1::text 
        AND created_at >= NOW() - INTERVAL '30 days'
      GROUP BY EXTRACT(HOUR FROM created_at)
      ORDER BY hour
    `, [userIdStr]);

    // 分析任务完成的时间分布
    const hourlyCompletionResult = await db.query(`
      SELECT 
        EXTRACT(HOUR FROM updated_at) as hour,
        COUNT(*) as count
      FROM tasks 
      WHERE user_id = $1::text 
        AND completed = true
        AND updated_at >= NOW() - INTERVAL '30 days'
      GROUP BY EXTRACT(HOUR FROM updated_at)
      ORDER BY hour
    `, [userIdStr]);

    // 分析工作日 vs 周末的效率
    const weekdayEfficiencyResult = await db.query(`
      SELECT 
        CASE 
          WHEN EXTRACT(DOW FROM updated_at) IN (0, 6) THEN 'weekend'
          ELSE 'weekday'
        END as day_type,
        COUNT(*) as completed_count,
        AVG(EXTRACT(EPOCH FROM (updated_at - created_at)) / 86400.0) as avg_completion_days
      FROM tasks 
      WHERE user_id = $1::text 
        AND completed = true
        AND updated_at >= NOW() - INTERVAL '30 days'
      GROUP BY day_type
    `, [userIdStr]);

    // 分析最高效的工作时段
    const peakHoursResult = await db.query(`
      SELECT 
        EXTRACT(HOUR FROM updated_at) as hour,
        COUNT(*) as completed_count,
        AVG(EXTRACT(EPOCH FROM (updated_at - created_at)) / 86400.0) as avg_completion_days
      FROM tasks 
      WHERE user_id = $1::text 
        AND completed = true
        AND updated_at >= NOW() - INTERVAL '30 days'
      GROUP BY EXTRACT(HOUR FROM updated_at)
      HAVING COUNT(*) >= 1
      ORDER BY completed_count DESC, avg_completion_days ASC
      LIMIT 3
    `, [userIdStr]);

    res.json({
      success: true,
      data: {
        hourlyCreation: hourlyCreationResult.rows.map(row => ({
          hour: parseInt(row.hour),
          count: parseInt(row.count)
        })),
        hourlyCompletion: hourlyCompletionResult.rows.map(row => ({
          hour: parseInt(row.hour),
          count: parseInt(row.count)
        })),
        weekdayEfficiency: weekdayEfficiencyResult.rows.map(row => ({
          dayType: row.day_type,
          completedCount: parseInt(row.completed_count),
          avgCompletionDays: row.avg_completion_days ? parseFloat(row.avg_completion_days).toFixed(1) : null
        })),
        peakHours: peakHoursResult.rows.map(row => ({
          hour: parseInt(row.hour),
          completedCount: parseInt(row.completed_count),
          avgCompletionDays: row.avg_completion_days ? parseFloat(row.avg_completion_days).toFixed(1) : null
        }))
      }
    });
  } catch (error) {
    console.error('获取工作模式分析失败:', error);
    res.status(500).json({ error: '获取工作模式分析失败' });
  }
});

// 获取拖延行为分析
router.get('/procrastination-analysis', async (req: Request, res: Response) => {
  try {
    const db = getDatabase();
    const userId = req.userId;
    const userIdStr = String(userId);

    // 分析拖延任务的特征 - 简化版本，避免复杂的日期转换
    const procrastinationResult = await db.query(`
      SELECT 
        priority,
        category,
        COUNT(*) as total_count,
        COUNT(CASE WHEN completed = true THEN 1 END) as completed_count,
        AVG(EXTRACT(EPOCH FROM (updated_at - created_at)) / 86400.0) as avg_completion_days
      FROM tasks 
      WHERE user_id = $1::text 
        AND created_at >= NOW() - INTERVAL '60 days'
      GROUP BY priority, category
      HAVING COUNT(*) >= 2
      ORDER BY avg_completion_days DESC
    `, [userIdStr]);

    // 分析任务创建的时间模式
    const creationPatternsResult = await db.query(`
      SELECT 
        EXTRACT(DOW FROM created_at) as day_of_week,
        EXTRACT(HOUR FROM created_at) as hour_of_day,
        COUNT(*) as task_count,
        COUNT(CASE WHEN completed = true THEN 1 END) as completed_count
      FROM tasks 
      WHERE user_id = $1::text 
        AND created_at >= NOW() - INTERVAL '60 days'
      GROUP BY EXTRACT(DOW FROM created_at), EXTRACT(HOUR FROM created_at)
      HAVING COUNT(*) >= 1
      ORDER BY task_count DESC
      LIMIT 10
    `, [userIdStr]);

    res.json({
      success: true,
      data: {
        procrastinationByCategory: procrastinationResult.rows.map(row => ({
          priority: row.priority,
          category: row.category,
          totalCount: parseInt(row.total_count),
          completedCount: parseInt(row.completed_count),
          completionRate: Math.round((row.completed_count / row.total_count) * 100),
          avgCompletionDays: row.avg_completion_days ? parseFloat(row.avg_completion_days).toFixed(1) : '0'
        })),
        creationPatterns: creationPatternsResult.rows.map(row => ({
          dayOfWeek: parseInt(row.day_of_week),
          hourOfDay: parseInt(row.hour_of_day),
          taskCount: parseInt(row.task_count),
          completedCount: parseInt(row.completed_count)
        }))
      }
    });
  } catch (error) {
    console.error('获取拖延行为分析失败:', error);
    res.status(500).json({ error: '获取拖延行为分析失败' });
  }
});

// 获取生产力建议
router.get('/productivity-suggestions', async (req: Request, res: Response) => {
  try {
    const db = getDatabase();
    const userId = req.userId;
    const userIdStr = String(userId);

    // 分析用户的任务完成情况
    const recentTasksResult = await db.query(`
      SELECT 
        priority,
        category,
        completed,
        created_at,
        updated_at
      FROM tasks 
      WHERE user_id = $1::text 
        AND created_at >= NOW() - INTERVAL '30 days'
      ORDER BY created_at DESC
    `, [userIdStr]);

    const tasks = recentTasksResult.rows;
    const suggestions = [];

    // 分析并生成建议
    const completedTasks = tasks.filter(t => t.completed);
    const pendingTasks = tasks.filter(t => !t.completed);

    // 建议1: 基于完成率
    const completionRate = tasks.length > 0 ? (completedTasks.length / tasks.length) * 100 : 0;
    if (completionRate < 70) {
      suggestions.push({
        type: 'completion_rate',
        priority: 'high',
        title: '提升任务完成率',
        description: `您的任务完成率为 ${completionRate.toFixed(1)}%，建议减少同时进行的任务数量，专注于重要任务。`,
        actionable: true
      });
    }

    // 建议2: 基于任务分布
    const categoryStats: Record<string, { total: number; completed: number }> = {};
    tasks.forEach(task => {
      if (!categoryStats[task.category]) {
        categoryStats[task.category] = { total: 0, completed: 0 };
      }
      categoryStats[task.category].total++;
      if (task.completed) categoryStats[task.category].completed++;
    });

    const lowPerformanceCategory = Object.entries(categoryStats)
      .map(([category, stats]: [string, any]) => ({
        category,
        completionRate: (stats.completed / stats.total) * 100,
        total: stats.total
      }))
      .filter(item => item.total >= 2)
      .sort((a, b) => a.completionRate - b.completionRate)[0];

    if (lowPerformanceCategory && lowPerformanceCategory.completionRate < 60) {
      suggestions.push({
        type: 'category_focus',
        priority: 'medium',
        title: `优化 ${lowPerformanceCategory.category} 类任务`,
        description: `该类别任务完成率较低（${lowPerformanceCategory.completionRate.toFixed(1)}%），建议重新评估任务难度或分配更多时间。`,
        actionable: true
      });
    }

    // 建议3: 基于时间管理
    if (pendingTasks.length > 10) {
      suggestions.push({
        type: 'task_load',
        priority: 'high',
        title: '优化任务负载',
        description: `您当前有 ${pendingTasks.length} 个待办任务，建议使用优先级管理，专注于 P0 和 P1 任务。`,
        actionable: true
      });
    }

    res.json({
      success: true,
      data: {
        suggestions,
        stats: {
          totalTasks: tasks.length,
          completedTasks: completedTasks.length,
          completionRate: completionRate.toFixed(1),
          avgDelayDays: '0'
        }
      }
    });
  } catch (error) {
    console.error('获取生产力建议失败:', error);
    res.status(500).json({ error: '获取生产力建议失败' });
  }
});

// 获取任务预测分析
router.get('/task-predictions', async (req: Request, res: Response) => {
  try {
    const db = getDatabase();
    const userId = req.userId;
    const userIdStr = String(userId);

    // 分析历史数据来预测任务完成时间
    const historicalDataResult = await db.query(`
      SELECT 
        priority,
        category,
        AVG(EXTRACT(EPOCH FROM (updated_at - created_at)) / 86400.0) as avg_completion_days,
        COUNT(*) as sample_size
      FROM tasks 
      WHERE user_id = $1::text 
        AND completed = true
        AND updated_at >= NOW() - INTERVAL '90 days'
      GROUP BY priority, category
      HAVING COUNT(*) >= 2
      ORDER BY avg_completion_days DESC
    `, [userIdStr]);

    // 获取当前待办任务
    const pendingTasksResult = await db.query(`
      SELECT 
        id,
        title,
        priority,
        category,
        created_at,
        due_date
      FROM tasks 
      WHERE user_id = $1::text 
        AND completed = false
      ORDER BY 
        CASE priority 
          WHEN 'P0' THEN 1 
          WHEN 'P1' THEN 2 
          WHEN 'P2' THEN 3 
          WHEN 'P3' THEN 4 
          ELSE 5 
        END,
        created_at DESC
      LIMIT 10
    `, [userIdStr]);

    // 创建预测模型
    const predictionModel: Record<string, { avgDays: number; sampleSize: number; confidence: number }> = {};
    historicalDataResult.rows.forEach(row => {
      const key = `${row.priority}_${row.category}`;
      predictionModel[key] = {
        avgDays: parseFloat(row.avg_completion_days),
        sampleSize: parseInt(row.sample_size),
        confidence: Math.min(parseInt(row.sample_size) / 5 * 100, 100)
      };
    });

    // 为待办任务生成预测
    const predictions = pendingTasksResult.rows.map(task => {
      const key = `${task.priority}_${task.category}`;
      const model = predictionModel[key];
      
      if (model) {
        const predictedCompletionDate = new Date(task.created_at);
        predictedCompletionDate.setDate(predictedCompletionDate.getDate() + Math.ceil(model.avgDays));
        
        return {
          taskId: task.id,
          title: task.title,
          priority: task.priority,
          category: task.category,
          predictedDays: Math.ceil(model.avgDays),
          predictedCompletionDate: predictedCompletionDate.toISOString().split('T')[0],
          confidence: Math.round(model.confidence),
          isLikelyOverdue: false,
          dueDate: task.due_date
        };
      } else {
        return {
          taskId: task.id,
          title: task.title,
          priority: task.priority,
          category: task.category,
          predictedDays: null,
          predictedCompletionDate: null,
          confidence: 0,
          isLikelyOverdue: false,
          dueDate: task.due_date,
          note: '数据不足，无法预测'
        };
      }
    });

    res.json({
      success: true,
      data: {
        predictions,
        model: Object.entries(predictionModel).map(([key, data]: [string, any]) => {
          const [priority, category] = key.split('_');
          return {
            priority,
            category,
            avgCompletionDays: data.avgDays.toFixed(1),
            sampleSize: data.sampleSize,
            confidence: Math.round(data.confidence)
          };
        })
      }
    });
  } catch (error) {
    console.error('获取任务预测失败:', error);
    res.status(500).json({ error: '获取任务预测失败' });
  }
});

export default router;